/*
	EXEC SP_DETAILED_TRANSACTION_REPORT_BY_LOT 214,'12/1/2008', '12/25/2008', 'Assembly', 'OSEP'
*/

ALTER PROC SP_DETAILED_TRANSACTION_REPORT_BY_LOT  @PROCESSSTEP_ID INT, 
										@FROM_DATE DATETIME, 
										@TO_DATE DATETIME,
										@PROCESS_TYPE VARCHAR(50),
										@VENDOR_NAME VARCHAR(50)
AS
BEGIN
	
	DECLARE @RESULTS_TABLE  TABLE ( 
 				PART_NUMBER VARCHAR(50),PROCESSSTEP_ID INT, LOT_NUMBER VARCHAR(50),
 				BEGINING_QTY INT, 
				IN_QTY INT,
				OUT_QTY INT,
				IN_FROM_FG INT, --TR
				IN_FROM_TR INT, --FG
				SHIPMENTS_TO_CUSTOMER INT, --FG
				SCRAP_QTY INT, 
				ENDING_QTY INT,
				DB_PART_NUMBER VARCHAR(50) )--ASSEMBLY 
	
	DECLARE @DB_PART_NUMBER VARCHAR(20)
	DECLARE @TMP VARCHAR(20)
	DECLARE @SEARCH_VENDOR_NAME VARCHAR(50)

	CREATE TABLE #BEGINING_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )
	CREATE TABLE #ENDING_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )
	DECLARE @BEGINIG_AS_OF_DATE DATETIME
	DECLARE @ENDING_AS_OF_DATE DATETIME
	SET @BEGINIG_AS_OF_DATE = DATEADD( ss, -1, @FROM_DATE )
	SET @ENDING_AS_OF_DATE = DATEADD( ss, -1, DATEADD( d, 1, @TO_DATE ) )
	--PRINT  @BEGINIG_AS_OF_DATE
	--PRINT  @ENDING_AS_OF_DATE

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@BEGINIG_AS_OF_DATE,101) AS DATETIME) ) --CURRENT WIP
	BEGIN
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS
				EXEC sp_wip_info_report @PROCESS_TYPE, @PROCESSSTEP_ID,@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
		ELSE --FOR FG WE ARE GETTING DIFFERENT RESULT SET
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_report @PROCESS_TYPE, @PROCESSSTEP_ID,@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
	END
	ELSE
	BEGIN
		SET @TMP = CONVERT(VARCHAR,@BEGINIG_AS_OF_DATE,101)
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0',@TMP,'0','all',''
		END
		ELSE
		BEGIN			

			INSERT INTO #BEGINING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0',@TMP,'0','all',''
		END
	END

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@ENDING_AS_OF_DATE,101) AS DATETIME) ) --CURRENT WIP
	BEGIN
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS
				EXEC sp_wip_info_report @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
		ELSE
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_report @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
				
		END
	END
	ELSE
	BEGIN
		SET @TMP = CONVERT(VARCHAR,@ENDING_AS_OF_DATE,101)
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0',@TMP,'0','all',''
		END
		ELSE
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,@PROCESSSTEP_ID,@VENDOR_NAME,'0',@TMP,'0','all',''
		END
	END
	IF ( @VENDOR_NAME IS NULL OR @VENDOR_NAME = '0' OR @VENDOR_NAME = '' )
	BEGIN
		SET @SEARCH_VENDOR_NAME = '%'
	END
	ELSE
	BEGIN
		SET @SEARCH_VENDOR_NAME = @VENDOR_NAME
	END
	INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, BEGINING_QTY )
		SELECT LOT_NUMBER, QTY_IN FROM  #BEGINING_WIP_RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, ENDING_QTY )
		SELECT LOT_NUMBER, QTY_IN FROM  #ENDING_WIP_RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, SCRAP_QTY )
		SELECT LT.LOT_NUMBER, SL.SL_SCRAPPED_DIE_QTY FROM SCRAPPED_LOTS SL, LOT_TABLE LT 
						WHERE SL.LOT_ID = LT.LOT_ID 
							AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID 
							AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) >= @FROM_DATE 
							AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) <= @TO_DATE  			

	IF ( @PROCESS_TYPE = 'Die Bank' )
	BEGIN
		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_QTY )
			SELECT LT.LOT_NUMBER, QTY_IN FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
					AND LT.FAB_LOT_NUMBER IN (
					SELECT DISTINCT FAB_LOT_NUMBER FROM SHIPPED_LOT SL, LOT_TABLE LT
							WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
							AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
							AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'FAB' )
								)
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, OUT_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
					WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND LT.ProcessStep_ID = @PROCESSSTEP_ID
					AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
					AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
	END
	ELSE IF ( @PROCESS_TYPE = 'Assembly' )
	BEGIN 
		SELECT @DB_PART_NUMBER = PART_NUMBER FROM PRODUCT P, PROCESS_STEP_TRAVERSE_TABLE PSTT, PROCESS_STEP PS
										WHERE P.PROCESSSTEP_ID = PSTT.PROCESSSTEP_ID
											AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
											AND PSTT.NEXT_PROCESSSTEP_ID = @PROCESSSTEP_ID
											AND PS.PS_TYPE_NAME = 'Die Bank'

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.LOT_NUMBER IN (SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS <> 'CANCELLED'
							AND ART.PROCESSSTEP_ID = @PROCESSSTEP_ID)
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, OUT_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'ASSEMBLY'
				AND SL.SL_NEXT_PROCESS <> 'ASSEMBLY' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
	END
	ELSE IF ( @PROCESS_TYPE = 'FT' )
	BEGIN
		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FT' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'Assembly' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, OUT_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'FT'
				AND SL.SL_NEXT_PROCESS <> 'FT'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
	END
	ELSE IF ( @PROCESS_TYPE = 'TAPE & REEL' )
	BEGIN
		DECLARE @RECEIVED_FROM_FTFG_QTY INT
		DECLARE @RECEIVED_FROM_FFG_QTY INT

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,0) WHERE PS_TYPE_NAME = 'TEST')
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME
		
		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_FROM_FG )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,0) WHERE PS_TYPE_NAME = 'FINISHED GOODS')
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_FROM_FG )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,1) WHERE PS_TYPE_NAME = 'FINISHED GOODS')
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, OUT_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Tape & Reel'
				AND SL.SL_NEXT_PROCESS <> 'Tape & Reel'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID	
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

	END
	ELSE IF ( @PROCESS_TYPE = 'FG' )
	BEGIN
		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'TEST' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, IN_FROM_TR )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'CLASS STORE' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME
		
		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, OUT_QTY )
			SELECT LT.LOT_NUMBER, SHIP_QTY FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'FG'
				AND SL.SL_NEXT_PROCESS <> 'FG'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID	
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

		INSERT INTO @RESULTS_TABLE ( LOT_NUMBER, SHIPMENTS_TO_CUSTOMER )
			SELECT LT.LOT_NUMBER, SUM(CSLNT_QTY_PULL)
				FROM CUSTOMER_SHIPPING_TABLE CST, 
					CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,
					CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, 
					CUSTOMER_OPEN_ORDER_LINEITEM COOL, 
					PRODUCT P,
					LOT_TABLE LT,
					LOCATION L
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID 
					AND CST.CST_STATUS = 'SHIPPED'
					AND CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
					AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID		
					AND P.PRODUCT_ID = COOL.PRODUCT_ID	
					AND LT.LOT_ID = CSLNT.LOT_ID	
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) >= @FROM_DATE 
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) <= @TO_DATE
					AND P.PROCESSSTEP_ID = @PROCESSSTEP_ID
					AND CSLNT_QTY_PULL > 0
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
				GROUP BY LT.LOT_NUMBER			
	END

	UPDATE @RESULTS_TABLE SET DB_PART_NUMBER = @DB_PART_NUMBER

	SELECT LOT_NUMBER, SUM(ISNULL(BEGINING_QTY,0)) AS BEGINING_QTY, 
		SUM(ISNULL(IN_QTY,0)) AS IN_QTY, 
		SUM(ISNULL(OUT_QTY,0)) AS OUT_QTY, 
		SUM(ISNULL(IN_FROM_FG,0)) AS IN_FROM_FG,
		SUM(ISNULL(IN_FROM_TR,0)) AS IN_FROM_TR,
		SUM(ISNULL(SHIPMENTS_TO_CUSTOMER,0)) AS SHIPMENTS_TO_CUSTOMER,
		SUM(ISNULL(SCRAP_QTY,0)) AS SCRAP_QTY,
		SUM(ISNULL(ENDING_QTY,0)) AS ENDING_QTY,
		DB_PART_NUMBER
	FROM @RESULTS_TABLE 
	GROUP BY LOT_NUMBER, DB_PART_NUMBER
END